Create Calendar Table, Measures and Calculated Columns 8
Measures:
1.
.Total
Attacks= COUNTROWS(DISTINCT(SAUSA50YRS[Case Number-ID]))
2.
.Avg Attacks Per Year = AVERAGEX(VALUES('Date'[Date].[Year]),[.Total
Attacks])
3.
.SA10YrsAgo = CALCULATE(SAUSA50YRS[.Avg Attacks Per
Year],DATEADD('Date'[Date], -10,YEAR))
4.
.DOD% = DIVIDE(([.Avg Attacks Per Year] -
[.SA10YrsAgo]),[.SA10YrsAgo])
5.
.ArrowForDOD% = SWITCH(TRUE(),[.DOD%]
<0,UNICHAR(9660),UNICHAR(9650))
6.
.ColorForArrow
= IF([.ArrowForDOD%] = UNICHAR(9660),"Green","Red")
Calculated Column
.Decades
= VAR DecadeStart =
ROUNDDOWN ( 'Date'[Date].[Year], -1 )
VAR
DecadeEnd = DecadeStart + 9
RETURN
DecadeStart & " - " &
DecadeEnd
Very useful video about how to create measures:
https://www.youtube.com/watch?v=MMZ0D8kSoMw
Measures:
1.
.Total
Attacks= COUNTROWS(DISTINCT(SAUSA50YRS[Case Number-ID]))
2.
.Avg Attacks Per Year = AVERAGEX(VALUES('Date'[Date].[Year]),[.Total
Attacks])
3.
.SA10YrsAgo = CALCULATE(SAUSA50YRS[.Avg Attacks Per
Year],DATEADD('Date'[Date], -10,YEAR))
4.
.DOD% = DIVIDE(([.Avg Attacks Per Year] -
[.SA10YrsAgo]),[.SA10YrsAgo])
5.
.ArrowForDOD% = SWITCH(TRUE(),[.DOD%]
<0,UNICHAR(9660),UNICHAR(9650))
6.
.ColorForArrow
= IF([.ArrowForDOD%] = UNICHAR(9660),"Green","Red")
Calculated Column
.Decades
= VAR DecadeStart =
ROUNDDOWN ( 'Date'[Date].[Year], -1 )
VAR
DecadeEnd = DecadeStart + 9
RETURN
DecadeStart & " - " &
DecadeEnd
Very useful video about how to create measures: